/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ipcc.utility.model;

import com.ipcc.manager.entity.IPCC_EXTENSIONS;
import com.ipcc.manager.model.IPCC_EXTENSION_GROUPSModel;
import com.ipcc.utility.entity.IPCC_CUSTOMER;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class IPCC_CUSTOMERModel {
    public static IPCC_CUSTOMER getByOrg_customer_id(String org_customer_id) throws Exception{
        Connection cn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String strSQL = "select customer_id,name,note,phone_number,org_customer_id"
                + " from ipcc_customers"
                + " where org_customer_id = ?";
        try {
            cn = AppServer.getConnection();
            pst = cn.prepareStatement(strSQL);
            
            pst.setString(1,org_customer_id);
            
            rs = pst.executeQuery();
            if (rs.next()) {
                IPCC_CUSTOMER i = new IPCC_CUSTOMER();                                
                i.setCustomer_id(Integer.parseInt(rs.getString(1)));
                i.setName(rs.getString(2));         
                i.setNote(rs.getString(3));
                i.setPhone_number(rs.getString(4));
                i.setOrg_customer_id(rs.getString(5));
                return i;
            }
            return null;
        } finally {
            Database.closeObject(rs);
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }
    
    public static IPCC_CUSTOMER getByPhone_number(String phone_number) throws Exception{
        Connection cn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String strSQL = "select customer_id,name,note,phone_number,org_customer_id"
                + " from ipcc_customers"
                + " where phone_number = ?";
        try {
            cn = AppServer.getConnection();
            pst = cn.prepareStatement(strSQL);
            
            pst.setString(1,phone_number);
            
            rs = pst.executeQuery();
            if (rs.next()) {
                IPCC_CUSTOMER i = new IPCC_CUSTOMER();                                
                i.setCustomer_id(Integer.parseInt(rs.getString(1)));
                i.setName(rs.getString(2));         
                i.setNote(rs.getString(3));
                i.setPhone_number(rs.getString(4));
                i.setOrg_customer_id(rs.getString(5));
                return i;
            }
            return null;
        } finally {
            Database.closeObject(rs);
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }
    
    public static boolean insert(IPCC_CUSTOMER ic) throws Exception{
        Connection cn = null;
        PreparedStatement pst = null;
        String strSQL = "INSERT INTO ipcc_customers(customer_id,name,org_customer_id,note,phone_number) "
                + " VALUES(seq_customer.nextval,?,?,?,?)";                
        try {
            cn = AppServer.getConnection();
            pst = cn.prepareStatement(strSQL);            
            pst.setString(1, ic.getName());                                                
            pst.setString(2, ic.getOrg_customer_id());
            pst.setString(3, ic.getNote());
            pst.setString(4, ic.getPhone_number());
            
            return pst.executeUpdate() > 0;            
        } finally {
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }
    public static boolean update(IPCC_CUSTOMER ic) throws Exception{
        Connection cn = null;
        PreparedStatement pst = null;
        String strSQL = "UPDATE ipcc_customers "
                + " SET NAME = ?,NOTE= ?,PHONE_NUMBER = ?"
                + " WHERE ORG_CUSTOMER_ID = ?";
        try {
            cn = AppServer.getConnection();
            pst = cn.prepareStatement(strSQL);
            
            pst.setString(1, ic.getName());            
            pst.setString(2, ic.getNote());
            pst.setString(3, ic.getPhone_number());            
            pst.setString(4, ic.getOrg_customer_id());
            
            return pst.executeUpdate() > 0;
        } finally {
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }
    
    public static String createDialString(String group_name,String last_dialled_number) throws Exception{
        String rs = "";        
        String group_id = IPCC_EXTENSION_GROUPSModel.getIdByName(group_name);
        List<IPCC_EXTENSIONS> listEx = IPCC_EXTENSIONS.getAll(" AND F.EXTENSION_GROUP = '"+group_id+"' ",null);
        List<Integer> listRandomNumber = getListRandomNumber(listEx.size());
        if(last_dialled_number != null && !last_dialled_number.equalsIgnoreCase("")){
            rs += "user/"+last_dialled_number;
            for(int i=0;i<listRandomNumber.size();i++){
                String extension = listEx.get(listRandomNumber.get(i)).getExtension();
                if(!extension.equals(last_dialled_number)){
                    rs += "|user/" + extension;
                }
            }
        }
        else for(int i=0;i<listRandomNumber.size();i++){
                String extension = listEx.get(listRandomNumber.get(i)).getExtension();
                if(i==0){
                    rs += "user/" + extension;
                }
                else rs += "|user/" + extension; 
            }
        return rs;
    }
    
    public static List<Integer> getListRandomNumber(int size){
        List<Integer> list = new ArrayList<Integer>();
        int i = 0;
        while(i < size){
            int rNumber = (int) (Math.random() * size);
            if(checkExistNumber(list, rNumber) == false){
                list.add(rNumber);
                i++;
            }
        }
        return list;
    }
    
    public static boolean checkExistNumber(List<Integer> list,int number){
        if(list == null || list.size() == 0){
            return false;
        } else{
            for(int i=0;i<list.size();i++){
                if(list.get(i) == number) return true;
            }
        }        
        return false;
    }                
}
